import copy
from time import time
from multiprocessing import Process, Queue
from utils.noticeUtils.dingtalkControl import DingTalkSendMsg
from utils.readFileUtils.excel_control import write_excle_data
from public_way import public_invoking


class Testishotdata:
    """
    Listing 迁移
    """

    # 全量数据初始化服务验证
    def base_comparison(self, id, erp_sql, cold_sql):
        if len(id) > 1:
            id_list = id.split(",")
        else:
            id_list = []
            id_list.append(id)

        # 创建队列
        q = Queue()
        eq = Queue()

        # 获取需要验证数据
        erp_data = public_invoking().erp_db(erp_sql)
        cold_data = public_invoking().erp_db(cold_sql)
        sum_erp = len(erp_data)
        sum_cold = len(cold_data)

        _colddata = []
        for i in cold_data:
            if i["ListingPrice"] != None:
                i["ListingPrice"] = float(i["ListingPrice"])
            i["SalePrice"] = float(i["SalePrice"])
            _colddata.append(i)
            # q.put(i)
        _erpdata = []
        for j in erp_data:
            if j["ListingPrice"] != None:
                j["ListingPrice"] = float(j["ListingPrice"])
            j["SalePrice"] = float(j["SalePrice"])
            _erpdata.append(j)

        # 数据比对
        res = public_invoking().data_comparison(_erpdata, _colddata)

        if sum_erp > sum_cold:
            error_data_list = public_invoking().data_comparison(_colddata, _erpdata)
            print(error_data_list)

        # # 创建进程
        # public_invoking().process_work(id, q, eq, _erpdata)

        # error_list = []
        # while not eq.empty():
        #     error_data = eq.get()
        #     error_list.append(error_data)

        if len(res) > 0:
            dingtalk_report = f"冷同步热：Listing出单,共检测{len(id_list)}个来源渠道  \n" \
                              f"共检测{sum_erp}条数据    \n" \
                              f"发现{len(res)}条异常数据    \n" \
                              f"异常数据明细:               \n" \
                              f"{res}"
            DingTalkSendMsg().send_migration_report(dingtalk_report)
        else:
            dingtalk_report = f"全量初始化共检测{len(id_list)}个来源渠道  \n" \
                              f"共检测{sum_erp}条数据    \n" \
                              f"发现{len(res)}条异常数据"
            DingTalkSendMsg().send_migration_report(dingtalk_report)

        return dingtalk_report

        # if len(res) == False:
        #     return True
        # else:
        #     return False

    # 冷热状态初始化服务验证
    def cold_hot_comparison(self, id, hot_sql, check=True):

        # 先判断数据初始化全量同步是否正常
        if check == True:
            # 创建队列
            q = Queue()
            eq = Queue()

            # 获取热数据sql并将OrderSourceID替换进去
            filedir = "D:\web_autotest\zhcx-service-test\Common\#热数据sql.csv"
            content = public_invoking().read_file(filedir).split('\n')
            content_sql = []
            for a in content:
                content_sql.append(a.replace("+", id))
            # 获取erp数据(去重)
            data_list = []
            for i in content_sql:
                data = public_invoking().erp_db(i)
                for j in data:
                    if j not in data_list:
                        j["ListingPrice"] = float(j["ListingPrice"])
                        data_list.append(j)
            sum_data = len(data_list)
            # 获取热数据表数据并推到队列
            hot_data = public_invoking().cold_db(hot_sql)
            for i in hot_data:
                # i["ListingPrice"] = float(i["ListingPrice"])
                q.put(i)

            # 创建进程
            public_invoking().process_work(q, eq, data_list)

            error_list = []
            while not eq.empty():
                error_data = eq.get()
                for j in error_data:
                    error_list.append(j)

            if len(error_list) > 0:
                dingtalk_report = f'''热数据初始化共检测{sum_data}条数据 \n''' \
                                  f'''发现{len(error_list)}条异常数据  \n''' \
                                  f'''异常数据明细:   \n{error_list}'''
                DingTalkSendMsg().send_migration_report(dingtalk_report)
            else:
                dingtalk_report = f'''热数据初始化共检测{sum_data}条数据    \n''' \
                                  f'''存在异常数据{len(error_list)}条  \n'''
                DingTalkSendMsg().send_migration_report(dingtalk_report)
        else:
            pass

    # 在售报告更新冷库
    def salereport_updata_colddb(self, cold_sql=None):

        q = Queue()
        eq = Queue()

        # 获取在售报告数据
        url = "http://dataservice-hd.zhcxkj.com/amazon/reports?customerId=1&orderSourceId=5005&date=2023-02-08"
        payload = ""
        res = public_invoking().requests_api("GET", url, payload)

        # 获取在售报告变更数据
        for i in res["list"]:
            i["IsOffShelf "] = 0
            i["SalePrice"] = float(i["price"])
            i["ASINorItemID"] = i["asin"]
            i["OrderSourceSKU"] = i["sku"]
            if i["quantity"] == "":
                i["IsFBA"] = 1
                i["ListingSoldNum"] = 0
            else:
                i["IsFBA"] = 0
                i["ListingSoldNum"] = int(i["quantity"])
            del i["price"], i["quantity"], i["asin"], i["sku"]
            q.put(i)

        # 获取冷库数据
        cold_data = public_invoking().cold_db(cold_sql)
        # 创建进程
        public_invoking().process_work(q, eq, cold_data)

    # 热同步冷 - 不符合热数据条件同步至打标表
    # 获取不满足热数据的冷数据
    def change_clod_data(self, id, hot_sql, sign_sql, cold_sql):

        # 获取冷数据
        erp_cold_data = public_invoking().get_cold_data(id, hot_sql)
        # 获取打标表数据
        sign_data = public_invoking().erp_db(sign_sql)
        copy_sign_data = copy.deepcopy(sign_data)
        for i in sign_data:
            del i["IsHotData"]
        error_data = public_invoking().data_comparison(sign_data, erp_cold_data)

        # 校验打标表同步状态至冷库表
        cold_db_data = public_invoking().cold_db(cold_sql)
        marking_error_data = public_invoking().data_comparison(copy_sign_data, cold_db_data)

        if len(error_data) == 0:
            error_data = "0条"
        else:
            error_data = ":" + error_data

        if len(marking_error_data) == 0:
            marking_error_data = "0条"
        else:
            marking_error_data = ":" + marking_error_data

        report = f"符合热转冷数据{len(erp_cold_data)}条   \n" \
                 f"同步至打标表 RB_Amazon_Product_Base_Marking_ColdSystem 数据{len(sign_data)}条    \n" \
                 f"发现异常数据{error_data}   \n" \
                 f"冷热状态标记异常数据{marking_error_data}"
        return report

    # 冷同步热 - 满足出单条件数据同步至打标表;打标表同步至临时表
    def hot_data_marking_base(self, id, erp_sql, marking_sql, temp_sql):

        issue_list = public_invoking().erp_db(erp_sql)

        lis = []
        for i in issue_list:
            for type, value in i.items():
                temps = value
                lis.append(str(temps))
        ordersourceskuid = ",".join(lis)

        cold_data = public_invoking().get_cold_marking(id, ordersourceskuid)
        marking_data = public_invoking().erp_db(marking_sql)

        marking_data_copy = copy.deepcopy(marking_data)
        sign_list = []
        for j in marking_data:
            j["SalePrice"] = float(j["SalePrice"])
            if j["IsHotData"]:
                j["IsHotData"] = 1
            else:
                j["IsHotData"] = 0
            sign_list.append(j)
        temp_data = public_invoking().erp_db(temp_sql)
        for i in marking_data_copy:
            del i["IsHotData"]

        sign_error_data = public_invoking().data_comparison(cold_data, marking_data)
        temp_error_data = public_invoking().data_comparison(temp_data, marking_data_copy)
        sum_error_data = sign_error_data + temp_error_data
        if len(sum_error_data) == 0:
            sum_error_data = "0条"
        else:
            sum_error_data = ":" + sum_error_data

        report = f"ERP满足出单数据{len(issue_list)}条   \n" \
                 f"同步至打标表 RB_Amazon_Product_Base_Marking数据{len(marking_data)}条    \n" \
                 f"同步至临时表 RB_Amazon_Product_Base_ColdHot_Temp数据{len(temp_data)}条    \n" \
                 f"发现异常数据{sum_error_data}"
        return report


if __name__ == '__main__':
    # # 满足出单同步至打标表
    # id = '5005'
    # erp_sql = f"SELECT OrderSourceSKUID FROM RB_Amazon_Product_Base ( NOLOCK ) WHERE OrderSourceSKUID IN ( SELECT id FROM dbo.RB_Product_OrderSourceSKU ( NOLOCK ) WHERE CustomerID = 1 AND OrderSourceID = {id} AND isDelete = 0 ) AND IsDelete = 0 AND AddTime < '2023-01-01';"
    # marking_sql = f"SELECT OrderSourceSKU, ASINorItemID, ListingPrice, SalePrice, IsHotData FROM RB_Amazon_Product_Base_Marking ( NOLOCK ) WHERE OrderSourceID = {id};"
    # temp_sql = f"SELECT OrderSourceSKU, ASINorItemID, ListingPrice, SalePrice FROM RB_Amazon_Product_Base_ColdHot_Temp ( NOLOCK ) WHERE OrderSourceID = {id};"
    # res = Testishotdata().hot_data_marking_base(id,erp_sql,marking_sql,temp_sql)
    # print(res)

    # 验证冷数据进打标表
    id = '15921'
    hot_sql = f"SELECT OrderSourceSKU, ASINorItemID, ListingPrice, SalePrice FROM RB_Amazon_Product_Base ( NOLOCK ) WHERE CustomerID = 1 AND OrderSourceSKUID > 0 AND OrdersourceID = {id} AND ID > 0 AND AddTime >= '2013/2/14 19:07:05' AND ISNULL( PublishTime, AddTime ) <= '2022/8/20 19:09:59';"
    sign_sql = f"SELECT OrderSourceSKU, ASINorItemID, ListingPrice, SalePrice, IsHotData FROM RB_Amazon_Product_Base_Marking_ColdSystem ( NOLOCK ) WHERE OrderSOurceID = {id} AND IsHotData = 0;"
    cold_sql = f"SELECT OrderSourceSKU, ASINorItemID, ListingPrice, SalePrice, IsHotData FROM aml_amazon_product_base_dep89 WHERE OrderSourceID = {id} AND IsHotData = 0;"
    res = Testishotdata().change_clod_data(id, hot_sql, sign_sql, cold_sql)
    print(res)

    # 数据同步验证
    # id = '5005'
    # erp_sql = f"SELECT OrderSourceSKU, ASINorItemID, ListingPrice, SalePrice FROM RB_Amazon_Product_Base ( NOLOCK ) WHERE OrderSourceSKUID IN ( SELECT id FROM dbo.RB_Product_OrderSourceSKU ( NOLOCK ) WHERE CustomerID = 1 AND OrderSourceID = {id} AND isDelete = 0 ) AND IsDelete = 0 AND AddTime < '2023-01-01';"
    # cold_sql = f"SELECT OrderSourceSKU, ASINorItemID, ListingPrice, SalePrice FROM RB_Amazon_Product_Base_Marking ( nolock ) WHERE OrderSourceID = {id};"
    # res = Testishotdata().base_comparison(id,erp_sql,cold_sql)
    # print(res)

    # sql = "SELECT IsOffShelf, SalePrice, ASINorItemID, OrderSourceSKU, CONVERT(IsFBA, SIGNED )AS IsFBA, ListingSoldNum FROM aml_amazon_product_base_dep89 WHERE OrderSourceID IN (15816,15921) AND UpdateTime BETWEEN '2023-02-01' AND '2023-02-10';"
    # Testishotdata().salereport_updata_colddb(sql)

    # id = '15921'
    # # all_sql = f"SELECT OrderSourceSKU, ASINorItemID, SKU, ListingPrice, SalePrice FROM RB_Amazon_Product_Base(nolock) " \
    # #           f"WHERE CustomerID = 1 AND OrderSourceID in ({id})  AND OrderSourceSKUID > 0 AND AddTime < '2023-01-01';"
    # erp_sql = f"SELECT OrderSourceSKU, ASINorItemID, SKU, ListingPrice, SalePrice, BrowsePathByID, OrderSourceName FROM RB_Amazon_Product_Base (NOLOCK) " \
    #           f"WHERE CustomerID =1 AND OrdersourceID IN ({id}) AND ID>0 AND OrderSourceSKUID > 0 AND ISNULL(PublishTime, AddTime) >= '2023-01-01' AND ISNULL(PublishTime, AddTime) < '2023-2-10' AND IsDelete=0 AND IsOffShelf<=2 AND OnShelfMode IN  (1, 2, 3, 4, 5, 6, 7);"
    # temp_sql = f"SELECT OrderSourceSKU, ASINorItemID, SKU, ListingPrice, SalePrice, BrowsePathByID, OrderSourceName FROM RB_Amazon_Product_Base_Marking_ColdSystem ( NOLOCK ) WHERE OrdersourceID IN ({id})"
    # all_mysql_sql = f"SELECT OrderSourceSKU, ASINorItemID, SKU, ListingPrice, SalePrice FROM aml_amazon_product_base_dep89 WHERE OrderSourceID in ({id});"
    # hot_sql = f"SELECT OrderSourceSKU, ASINorItemID, SKU, ListingPrice, SalePrice FROM aml_amazon_product_base_dep89 WHERE OrderSourceID in ({id}) AND IsHotData = 1;"
    # res = Testishotdata().base_comparison(id,erp_sql,temp_sql)
    # print(res)

    # hot_sql = f"SELECT OrderSourceSKU, ASINorItemID, SKU, ListingPrice, SalePrice FROM RB_Amazon_Product_Base ( NOLOCK ) WHERE CustomerID = 1 AND OrderSourceSKUID > 0 AND OrdersourceID = {id} AND ID > 0 AND AddTime >= '2013/2/14 19:07:05' AND ISNULL( PublishTime, AddTime ) <= '2022/8/18 19:08:33'"
    # Testishotdata().cold_hot_comparison(id,hot_sql)
    #
    # id = "15921,15816"
    # sql = f"SELECT OrderSourceSKU, ASINorItemID, SKU, ListingPrice FROM  RB_Amazon_Product_Base (NOLOCK)  WHERE CustomerID =1 AND OrdersourceID IN ({id}) AND ID>0 AND OrderSourceSKUID > 0 AND ISNULL(PublishTime, AddTime)>='2023-02-07' AND ISNULL(PublishTime, AddTime)<'2023-2-10' AND IsDelete=0 AND IsOffShelf<=2 AND OnShelfMode IN  (1, 2, 3, 4, 5, 6, 7);"
    # cold_sql = f"SELECT OrderSourceSKU, ASINorItemID, SKU, SalePrice AS ListingPrice FROM aml_amazon_product_base_dep89 WHERE CustomerID =1 AND OrderSourceID IN ({id}) AND AddTime>'2023-02-01';"
    # Testishotdata().base_comparison(id, erp_sql=sql, cold_sql=cold_sql)
